Data Import pages are used for the importing of records that have been exported from other systems in comma delimited (csv) format. Data Management Queries are used to create import templates that map fields from the exported file to MiiNT tables. The import data management queries can be assigned to navigation menu items with a page type of Data Import. Only users with access to these navigation menus will be able to access these tables.
In the above example a Data Management Query may be used to import records into the Location table and the related Location Coordinates Bulk Upload table.
Note: It is now possible to select related tables, that may be used when importing records into a table and its related table(s). The fields for the related table(s) will be listed amongst the fields of the selected table code and mapped to fields in an exported comma delimited (csv) file.
Note: It may be necessary to set the order of the Id fields when importing from related tables that have a dependency. For instance when creating new hole details using the Location, Hole Parameters and Hole Positions tables the location will need to exist before the hole parameter records can be created and the hole parameter records will need to exist before the hole position records can be created.
Refer to the Data Management Queries Fields topic for information relating to the data management queries fields and attribute fields.
Note: To display any changes made to any of the Data Management Queries select the Refresh Items button on the relevant menu to pick up the changes.
Refer to the Managing Administration Definition Types topic for information on how to manage the Data Management Queries - Administration definitions.
The Default New Value attribute in data management queries is used to map fields in the comma delimited (csv) file to fields from the selected Table Code and Related Table(s). The following options are supported when mapping fields from the import file to tables within MiiNT:
The table below list the row values that are supported for mapping the values between the csv file and the MiiNT table(s).
| Row Values | Description |
|---|---|
|
{A} |
Maps to the value of the first column. Uses the same column naming as excel. Note: This works for csv files with and without headers. |
|
{1} |
Maps to the index of the column in the csv file. In this example it would be mapped to the second column (or column B). |
|
{%Name} |
Maps to the column with the specified name and is case sensitive. Note: This will only work for csv files with headers. |
|
{/Code} |
This maps to another field in the writeback table by using the code path of that field as the reference. This is usefull when there is complex logic in one field, another field can just use the resulting value. For example a location code may be made up of segment 1 and 2 separated by an underscore, this mean a value for the location code such as "{/S1}_{S2}" could be used. This means that the S1 and S2 fields can contain the complex logic to ensure naming convention, and then the code just uses the resulting value. |
The table below list the conditional expressions that are supported for mapping the values between the csv file and the MiiNT table(s).
|
Conditional Expressions |
Description |
|---|---|
|
IIF( {A} = "ABC" , "True" , "False" ) |
Perform different actions based on the result of a conditional statement. |
|
Switch( {A} ,"Match1","Result1" ,"Match2","Result2" ... , "Match#","Result#" ,"DefaultValue") |
Useful for mapping multiple values. |
Note: All expressions must be wrapped in _Expression( <expression goes here> ).
Note: All types of row values can be used within (and along side) expressions. When Row Values are used within an expression they must be enclosed in double quotes eg _Expression(Left( "{%Pit}", 3))
The table below list the format expressions that are supported for mapping the values between the csv file and the MiiNT table(s).
|
Format Expressions |
Description |
|---|---|
|
Format( "format", value ) |
Format a value. |
|
Number formatting examples |
Note: Refer to the Numeric Format String web page for more information. |
|
Date formatting examples |
Refer to the Date and Time Format String web page for more information. |
Note: All expressions must be wrapped in _Expression( <expression goes here> ).
Note: All types of row values can be used within (and along side) expressions. When Row Values are used within an expression they must be enclosed in double quotes eg _Expression(Left( "{%Pit}", 3))
The table below list the Math expressions that are supported for mapping the values between the csv file and the MiiNT table(s).
|
Math Expressions |
Description |
|---|---|
|
Round( value, decimalPlaces ) |
Round a number to specified decimal places. |
|
Floor( value ) |
Round a number down to the nearest integer value. |
|
Ceiling ( value ) |
Round a number up to the nearest integer value. |
|
Sin / Cos / Tan( value ) |
Get the relevant trigonometric value. |
|
ASin / ACos / ATan( value ) |
Get the relevant inverse trigonometric value. |
|
Sinh / Cosh / Tanh( value ) |
Get the relevant hyberbolic value. |
|
Abs( value ) |
The absolute value. |
|
Power( value, e ) |
Value to the power of e. |
|
Sqrt( value ) |
Square root of the value. |
|
Log( value ) |
Logarithmic value. |
|
Min / Max( value1, value2 ) |
Minimum or maximum value between multiple values. |
|
Pi() |
Gets the value of Pi. |
|
ConvertDegToRad( degrees ) |
Converts degrees value to radians. |
|
ConvertRadToDeg( radians ) |
Converts radians value to degrees. |
|
ConvertDegToBearing( degrees ) |
Converts Bearing value to Degrees. |
Note: All expressions must be wrapped in _Expression( <expression goes here> ).
Note: All types of row values can be used within (and along side) expressions. When Row Values are used within an expression they must be enclosed in double quotes eg _Expression(Left( "{%Pit}", 3))
The table below list the Text expressions that are supported for mapping the values between the csv file and the MiiNT table(s).
|
Text Expressions |
Description |
|---|---|
|
Len( "text" ) |
Gets the length of a string. |
|
Mid( "text", start, length ) |
Get part of a string (similar to substring). |
|
Mid( "text", start ) |
Get part of a string (similar to substring). |
|
Left( "text", length) |
Gets the left characters of a string. |
|
Right( "text", length ) |
Gets the right characters of a string. |
|
Replace( "text" , "replaceThis", "withThis" ) |
Replace text within a string. |
|
Upper / Lower( "text" ) |
Converts the string to Uppercase or Lowercase. |
|
Wcase( "text" ) |
Converts the first letter to uppercase, rest as lowercase. |
|
Trim / LTrim / RTrim( "text" ) |
Trim whitespace from a string (or just the left/right parts of the string). |
|
PadLeft / PadRight( "text", length, " ") |
Adds padding to text of specified length, using the specified padding character. |
|
Repeat( number, " " ) |
Repeat a string a number of times. |
|
InStr( "text", "search", startPos ) |
Finds the positions of text within a string, starting from the startPos. |
|
InStrRev( "text", "search" ) |
Finds the positions of text within a string starting from the end of the string and searching backwards. |
|
CStr( value ) |
Convert number or date to string. |
|
ConcatString( "v1", "v2", " " ) |
Concatenate values with a separator to build a string. |
Note: All expressions must be wrapped in _Expression( <expression goes here> ).
Note: All types of row values can be used within (and along side) expressions. When Row Values are used within an expression they must be enclosed in double quotes eg _Expression(Left( "{%Pit}", 3))
The table below list the Date Time expressions that are supported for mapping the values between the csv file and the MiiNT table(s).
|
Date Time Expressions |
Description |
|---|---|
|
Now() |
Gets the current date and time. |
|
Today() |
Gets the current date. |
|
Date( "01-01-2001" ) |
Convert a string to a date. |
|
Date( year, month, day ) |
Create a date from year, month and day values. |
|
DateTime( "01-01-2001 10:00 PM") |
Create a date time from a string. |
|
DateTimeToLocalTime( "2009 May 27 10:30 PM") |
Convert a UTC date time to a local date time. |
|
DateTimeToUniversalTime( "2009 May 27 10:30 PM") |
Convert a local date time to a UTC date time. |
|
Year / Month / Day( datevalue ) |
Gets the respective date value from a date. |
|
MonthName / MonthNameShort( datevalue ) |
Gets the name of the month from a date. |
|
DayOfYear( datevalue ) |
Gets the day of the year value from a date. |
|
DayOfWeek( datevalue ) |
Gets the day number of the week from a date. |
|
Hour / Minute / Second( datevalue ) |
Gets the given time value from a date time. |
|
DateDiff( measure, date1, date2 ) |
Gets the difference between two dates using one of the given measure values:
|
|
DateAdd( date, years, months, days ) |
Add the given years, months and days to a given date. |
Note: All expressions must be wrapped in _Expression( <expression goes here> ).
Note: All types of row values can be used within (and along side) expressions. When Row Values are used within an expression they must be enclosed in double quotes eg _Expression(Left( "{%Pit}", 3))
The table below list the Miscellaneous expressions that are supported for mapping the values between the csv file and the MiiNT table(s).
|
Date Time Expressions |
Description |
|---|---|
|
Null() |
Null value. |
|
IsNull( value ) |
Check if a value is null. |
|
IfNull( value, default ) |
Check if a value is null and use the default value when it is null. |
|
Guid() |
Generate a new global unique id. |
|
IsGuid( value ) |
Checks if a value is a valid Guid. |
Note: All expressions must be wrapped in _Expression( <expression goes here> ).
Note: All types of row values can be used within (and along side) expressions. When Row Values are used within an expression they must be enclosed in double quotes eg _Expression(Left( "{%Pit}", 3))
Note: Most functions that can be used to create expressions in Query Builder are supported.